package com.upcode.weighing.util.excel;

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.upcode.weighing.util.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.EmptyFileException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.DocumentFactoryHelper;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.util.IOUtils;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;


/**
 * Created with IntelliJ IDEA.
 * Description:
 *
 * @author : liqi
 * Date: 2018-12-10
 * Time: 14:05
 */
@Slf4j
public class EasyExcelUtils {

    /**
     * 导出根据实体模板
     *
     * @param response
     * @param list
     * @param name
     * @param clazz
     */
    public static void generateExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String name, Class<? extends BaseRowModel> clazz) throws UnsupportedEncodingException {

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/x-msdownload");
        response.addHeader("Content-Type", "application/octet-stream");
        String nameCode = URLEncoder.encode(name, "UTF-8");
        response.addHeader("content-disposition", "attachment;filename=" + nameCode + ".xls");
        ServletOutputStream out = null;
        ExcelWriter writer = null;
        try {
            out = response.getOutputStream();
            writer = new ExcelWriter(out, ExcelTypeEnum.XLS, true);
            Sheet sheet1 = new Sheet(1, 3, clazz);
            sheet1.setSheetName("sheet");
            writer.write(list, sheet1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(writer != null) {
            	writer.finish();
            }
            if(out != null) {
            	 try {
                     out.close();
                 } catch (IOException e) {
                     // e.printStackTrace();
                 }
            }
            
           
        }
    }


    /**
     * 导出 Excel ：一个 sheet，带表头
     *
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param clazz     映射实体类，Excel 模型
     */
    public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
                                  String fileName, String sheetName, Class<? extends BaseRowModel> clazz) throws IOException {
        //创建本地文件
        String filePath = fileName + ".xlsx";
        File dbfFile = new File(filePath);
        if (!dbfFile.exists() || dbfFile.isDirectory()) {
            dbfFile.createNewFile();
        }
        fileName = new String(filePath.getBytes(), "ISO-8859-1");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        OutputStream out = response.getOutputStream();
        try {
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
            Sheet sheet = new Sheet(1, 0, clazz);
            sheet.setSheetName(sheetName);
            writer.write(list, sheet);
            writer.finish();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                //e.printStackTrace();
            }
        }
    }


    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    public static ExcelReader getReader(MultipartFile excel,
                                        ExcelListener excelListener) {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new RuntimeException("上传文件格式错误，请上传后缀名为.xls/.xlsx的表格文件！");
        }
        ExcelTypeEnum excelTypeEnum = ExcelTypeEnum.XLSX;
        if (filename.toLowerCase().endsWith(".xls")) {
            excelTypeEnum = ExcelTypeEnum.XLS;
        }
        InputStream inputStream;
        try {
            inputStream = excel.getInputStream();
            return new ExcelReader(inputStream, excelTypeEnum,
                    excelListener);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * @param in            文件输入流
     * @param customContent 自定义模型可以在
     *                      AnalysisContext中获取用于监听者回调使用
     * @param eventListener 用户监听
     * @throws IOException
     * @throws EmptyFileException
     * @throws InvalidFormatException
     */
    public static ExcelReader getExcelReader(InputStream in, Object customContent,
                                             AnalysisEventListener<?> eventListener) throws Exception {
        // 如果输入流不支持mark/reset，需要对其进行包裹
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }

        // 确保至少有一些数据
        byte[] header8 = IOUtils.peekFirst8Bytes(in);
        ExcelTypeEnum excelTypeEnum = null;
        if (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
            excelTypeEnum = ExcelTypeEnum.XLS;
        }
        if (DocumentFactoryHelper.hasOOXMLHeader(in)) {
            excelTypeEnum = ExcelTypeEnum.XLSX;
        }
        if (excelTypeEnum != null) {
            return new ExcelReader(in, excelTypeEnum, customContent, eventListener);
        }
        throw new InvalidFormatException("文件流读取错误！");

    }

    /**
     * @param in            文件输入流
     * @param customContent 自定义模型可以在
     *                      AnalysisContext中获取用于监听者回调使用
     * @param eventListener 用户监听
     * @param trim          是否对解析的String做trim()默认true,用于防止 excel中空格引起的装换报错。
     * @throws IOException
     * @throws EmptyFileException
     * @throws InvalidFormatException
     */
    public static ExcelReader getExcelReader(InputStream in, Object customContent,
                                             AnalysisEventListener<?> eventListener, boolean trim)
            throws EmptyFileException, IOException, InvalidFormatException {
        // 如果输入流不支持mark/reset，需要对其进行包裹
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }
        // 确保至少有一些数据
        byte[] header8 = IOUtils.peekFirst8Bytes(in);
        ExcelTypeEnum excelTypeEnum = null;
        if (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
            excelTypeEnum = ExcelTypeEnum.XLS;
        }
        if (DocumentFactoryHelper.hasOOXMLHeader(in)) {
            excelTypeEnum = ExcelTypeEnum.XLSX;
        }
        if (excelTypeEnum != null) {
            return new ExcelReader(in, excelTypeEnum, customContent, eventListener, trim);
        }
        throw new InvalidFormatException("文件流读取错误！");
    }


    /**
     * @param response
     * @param list         要倒出的数据集合
     * @param name
     * @param templateName 模板名称   模板位于resources/excel_template 目录下
     * @throws Exception
     */
    public static void generateExcelWithTemplate(HttpServletResponse response, List<? extends Object> list, String name, String templateName) {
        ServletOutputStream out = null;
        InputStream inputStream = null;
        try {
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-msdownload");
            response.addHeader("Content-Type", "application/octet-stream");
            String nameCode = URLEncoder.encode(name, "UTF-8");
            response.addHeader("content-disposition", "attachment;filename=" + nameCode + ".xls");
            String templatePath = "excel_template" + File.separator + templateName;
            ClassPathResource classPathResource = new ClassPathResource(templatePath);
            inputStream = classPathResource.getInputStream();
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
            HSSFWorkbook Workbook = new HSSFWorkbook(poifsFileSystem);
//            XSSFWorkbook Workbook = new XSSFWorkbook(inputStream);//得到文档对象
            HSSFSheet sheet = Workbook.getSheetAt(0);
            int start = sheet.getLastRowNum() + 1;
            for (int i = 0; i < list.size(); i++) {
                HSSFRow startRow = sheet.createRow(start + i);
                HSSFCell cell = startRow.createCell(0);
                cell.setCellValue(i+1);
                Object obj = list.get(i);
                Field[] declaredFields = obj.getClass().getDeclaredFields();
                for (int j = 0; j < declaredFields.length; j++) {
                    Field field = declaredFields[j];
                    field.setAccessible(true);
                    HSSFCell rowCell = startRow.createCell(j+1);
                    Object objValue = field.get(obj);
                    if (objValue == null) {
                        rowCell.setCellValue("");
                    } else {
                        String typeName = field.getType().getName();
                        String[] names = typeName.split("\\.");
                        if (names.length > 0) {
                            String name1 = names[names.length - 1];
                            if ("LocalDateTime".equals(name1)) {
                                LocalDateTime dateTime = (LocalDateTime) objValue;
                                String format = dateTime.format(DateTimeFormatter.ofPattern(DateUtils.PATTERND_TIME));
                                rowCell.setCellValue(format);
                            } else if ("LocalDate".equals(name1)) {
                                LocalDate dateTime = (LocalDate) objValue;
                                String format = dateTime.format(DateTimeFormatter.ofPattern(DateUtils.PATTERN_DATE));
                                rowCell.setCellValue(format);
                            } else {
                                rowCell.setCellValue(field.get(obj).toString());
                            }
                        } else {
                            rowCell.setCellValue(field.get(obj).toString());
                        }
                    }
                }
//            sheet.shiftRows(sheet.getLastRowNum(), sheet.getLastRowNum(), 1);
            }
            out = response.getOutputStream();
            if(out != null) {
            	out.flush();
                Workbook.write(out);
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                inputStream.close();
                out.close();
            } catch (Exception e) {

            }
        }

    }

}
